﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Get_Saved_Type_List')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Get_Saved_Type_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Get_Saved_Type_List
**	Desc : 종류별 근력운동을 조회한다.
**	Test Exec Query : Exec usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Get_Saved_Type_List 'TR00000165', '','ko'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING_PARTS.cs
**	Program ID : Fit1011m
**	Auth : 송시명
**	Date : 2014-05-21
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Get_Saved_Type_List]
@ls_training_id nvarchar(10),
@ls_training_part nvarchar(max),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @training_parts nvarchar(max)
IF @ls_training_part = ''
BEGIN
    SET @training_parts = '%'
END
ELSE
BEGIN
    SET @training_parts = @ls_training_part
END

SELECT ROW_NUMBER() OVER(ORDER BY X.Training_Part, X.View_Order ASC) AS Rnum,
       ISNULL(rslt.Seq, 0) AS Seq,
       X.Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(X.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       X.Training_Nm,
       rslt.Training_Id,
       X.Training_Type_Id,
       rslt.Action_Weight,
       rslt.Action_Count,
       rslt.Set_Count,
       rslt.Remark,
       '<div title="요약" class="sbtn sbtn_summary" onclick="goTrainingTypeSummaryPage(''' + X.Training_Type_Id + ''');"></div>' AS Summary,
       CASE ISNULL(rslt.Training_Id, 'N')
            WHEN 'N' THEN 'N'
            ELSE 'Y'
       END Save_Yn,
	   X.Weight_Calc_Rate
  FROM UPDMS_FIT_TRAINING_TYPE AS X WITH(NOLOCK)
  LEFT OUTER JOIN (
       SELECT A.Seq,
              A.Training_Id,
              A.Training_Type_Id,
              A.Action_Weight,
              A.Action_Count,
              A.Set_Count,
              A.Remark
         FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS AS A WITH(NOLOCK)
         JOIN UPDMS_FIT_WEIGHT_TRAINING AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
        WHERE A.Training_Id = @ls_training_id
       ) rslt
    ON X.Training_Type_Id = rslt.Training_Type_Id
 WHERE X.Use_Yn = 'Y'
   AND (X.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
       OR X.Training_Part LIKE @training_parts
       )

GO